package com.jeff.tianti.cms.dao;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.jeff.tianti.cms.dto.SearchKewWordDto;
import com.jeff.tianti.cms.dto.font.ArticleFontQueryDTO;
import com.jeff.tianti.common.PageBean;
import com.jeff.tianti.common.util.DateUtils;
import org.apache.commons.lang3.StringUtils;

import com.jeff.tianti.cms.dto.ArticleQueryDTO;
import com.jeff.tianti.cms.dto.CurrentArticleInfoDTO;
import com.jeff.tianti.cms.entity.Article;
import com.jeff.tianti.common.dao.CustomBaseSqlDaoImpl;
import com.jeff.tianti.common.entity.PageModel;
import org.springframework.util.CollectionUtils;

/**
 * @author xujianfang
 * @desc ArticleDaoImpl类
 * @date 2017-03-16
 */

public class ArticleDaoImpl extends CustomBaseSqlDaoImpl implements ArticleDaoCustom {

    public PageModel<Article> queryArticlePage(ArticleQueryDTO articleQueryDTO) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuilder hql = new StringBuilder();
        hql.append("select t from Article t where 1=1 ");
        if (articleQueryDTO != null) {
            if (StringUtils.isNotBlank(articleQueryDTO.getRootColumnId())) {
                hql.append(" and t.rootColumnInfo.id = :rootColumnId ");
                map.put("rootColumnId", articleQueryDTO.getRootColumnId());
            }
            if (StringUtils.isNotBlank(articleQueryDTO.getColumnId())) {
                hql.append(" and t.columnInfo.id = :columnId ");
                map.put("columnId", articleQueryDTO.getColumnId());
            }
            if (StringUtils.isNotBlank(articleQueryDTO.getTitle())) {
                hql.append(" and t.title like  :title ");
                map.put("title", "%" + articleQueryDTO.getTitle() + "%");
            }
            if (StringUtils.isNotBlank(articleQueryDTO.getPublisher())) {
                hql.append(" and t.publisher like  :publisher ");
                map.put("publisher", "%" + articleQueryDTO.getPublisher() + "%");
            }
            if (StringUtils.isNotBlank(articleQueryDTO.getDeleteFlag())) {
                hql.append(" and t.deleteFlag = :deleteFlag ");
                map.put("deleteFlag", articleQueryDTO.getDeleteFlag());
            }
            if (StringUtils.isNotBlank(articleQueryDTO.getStartDate())) {
                hql.append(" and t.createDate  >= :startDate ");
                try {
                    map.put("startDate", sdf.parse(articleQueryDTO.getStartDate()));
                } catch (ParseException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if (StringUtils.isNotBlank(articleQueryDTO.getEndDate())) {
                hql.append(" and t.createDate  <= :endDate ");
                try {
                    map.put("endDate", sdf.parse(articleQueryDTO.getEndDate()));
                } catch (ParseException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if (articleQueryDTO.getIsFront() != null && articleQueryDTO.getIsFront()) {
                //前端默认查询审核通过的且未删除的记录，且按照置顶和时间降序排序
                hql.append(" and t.isAudit =1  order by t.isTop desc,t.createDate desc ");
            } else {
                if (StringUtils.isNotBlank(articleQueryDTO.getType())) {
                    if (articleQueryDTO.getType().equals("laji")) {
                        hql.append(" and t.deleteFlag =1 ");
                    } else if (articleQueryDTO.getType().equals("zhiding")) {
                        hql.append(" and t.isTop =1 ");
                    }
                    hql.append(" order by t.createDate desc ");
                }
            }
        }
        return this.queryForPageWithParams(hql.toString(), map, articleQueryDTO.getCurrentPage(), articleQueryDTO.getPageSize());
    }

    public List<Article> queryArticleList(ArticleQueryDTO articleQueryDTO) {
        List<Article> articleList = null;
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuilder hql = new StringBuilder();
        hql.append("select t from Article t where 1=1 ");
        if (articleQueryDTO != null) {
            if (StringUtils.isNotBlank(articleQueryDTO.getColumnId())) {
                hql.append(" and t.columnInfo.id = :columnId ");
                map.put("columnId", articleQueryDTO.getColumnId());
            }
            if (StringUtils.isNotBlank(articleQueryDTO.getTitle())) {
                hql.append(" and t.title like = :title ");
                map.put("title", "%" + articleQueryDTO.getTitle() + "%");
            }
            if (StringUtils.isNotBlank(articleQueryDTO.getPublisher())) {
                hql.append(" and t.publisher like = :publisher ");
                map.put("publisher", "%" + articleQueryDTO.getPublisher() + "%");
            }
            if (StringUtils.isNotBlank(articleQueryDTO.getDeleteFlag())) {
                hql.append(" and t.deleteFlag = :deleteFlag ");
                map.put("deleteFlag", articleQueryDTO.getDeleteFlag());
            }
            if (StringUtils.isNotBlank(articleQueryDTO.getStartDate())) {
                hql.append(" and t.createDate  >= :startDate ");
                map.put("startDate", articleQueryDTO.getStartDate());
            }
            if (StringUtils.isNotBlank(articleQueryDTO.getEndDate())) {
                hql.append(" and t.createDate  <= :endDate ");
                map.put("endDate", articleQueryDTO.getEndDate());
            }
            if (articleQueryDTO.getIsFront() != null && articleQueryDTO.getIsFront()) {
                //前端默认查询审核通过的且未删除的记录，且按照置顶和时间降序排序
                hql.append(" and t.isAudit =1  order by t.isTop desc,t.createDate desc ");
            } else {
                if (StringUtils.isNotBlank(articleQueryDTO.getType())) {
                    if (articleQueryDTO.getType().equals("laji")) {
                        hql.append(" and t.deleteFlag =1 ");
                    } else if (articleQueryDTO.getType().equals("zhiding")) {
                        hql.append(" and t.isTop =1 ");
                    }
                    hql.append(" order by t.createDate desc ");
                }
            }
        }
        if (articleQueryDTO.getTop() != null) {
            PageModel pageModel = this.queryForPageWithParams(hql.toString(), map, 0, articleQueryDTO.getTop());
            if (pageModel != null) {
                articleList = pageModel.getList();
            }
        } else {
            articleList = this.queryByMapParams(hql.toString(), map);
        }
        return articleList;
    }

    public List<Map<String, Object>> queryStatisMapList(ArticleQueryDTO articleQueryDTO) {
        String sql = " select count(1) as totalCount,(select count(1) from cms_article where is_top=1 and delete_flag = '0') as zhidingCount,(select count(1) from cms_article where is_audit=1 and delete_flag = '0') as shenheCount,(select count(1) from cms_article where delete_flag='1') as deleteCount from cms_article a  ";
        return this.querySqlObjects(sql);
    }

    public List<Article> queryNextArticleList(CurrentArticleInfoDTO currentArticleInfoDTO) {
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuilder hql = new StringBuilder();
        hql.append(" select a from Article a where 1=1  ");
        if (StringUtils.isNotBlank(currentArticleInfoDTO.getArticleId())) {
            hql.append(" and a.id <> :articleId ");
            map.put("articleId", currentArticleInfoDTO.getArticleId());
        }
        if (StringUtils.isNotBlank(currentArticleInfoDTO.getColumnId())) {
            hql.append(" and a.columnInfo.id = :columnInfoId ");
            map.put("columnInfoId", currentArticleInfoDTO.getColumnId());
        }
        if (currentArticleInfoDTO.getArticleDate() != null) {
            hql.append(" and a.createDate < :date ");
            map.put("date", currentArticleInfoDTO.getArticleDate());
            hql.append(" order by a.createDate desc ");
        } else if (currentArticleInfoDTO.getOrderNo() != null) {
            hql.append(" and a.orderNo < :orderNo ");
            map.put("orderNo", currentArticleInfoDTO.getOrderNo());
            hql.append(" order by a.orderNo desc ");
        }
        return this.queryByMapParams(hql.toString(), map);
    }

    public List<Article> queryPreArticleList(CurrentArticleInfoDTO currentArticleInfoDTO) {
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuilder hql = new StringBuilder();
        hql.append(" select a from Article a where 1=1  ");
        if (StringUtils.isNotBlank(currentArticleInfoDTO.getArticleId())) {
            hql.append(" and a.id <> :articleId ");
            map.put("articleId", currentArticleInfoDTO.getArticleId());
        }
        if (StringUtils.isNotBlank(currentArticleInfoDTO.getColumnId())) {
            hql.append(" and a.columnInfo.id = :columnInfoId ");
            map.put("columnInfoId", currentArticleInfoDTO.getColumnId());
        }
        if (currentArticleInfoDTO.getArticleDate() != null) {
            hql.append(" and a.createDate > :date ");
            map.put("date", currentArticleInfoDTO.getArticleDate());
            hql.append(" order by a.createDate asc ");
        } else if (currentArticleInfoDTO.getOrderNo() != null) {
            hql.append(" and a.orderNo > :orderNo ");
            map.put("orderNo", currentArticleInfoDTO.getOrderNo());
            hql.append(" order by a.orderNo asc ");
        }
        return this.queryByMapParams(hql.toString(), map);
    }

    public List<Map<String, Object>> queryArticleById(String id) {
        StringBuilder hql = new StringBuilder();
        hql.append(" SELECT id,DATE_FORMAT(create_date,'%Y-%m-%d') as createDate ,title ,publisher,source_from as sourceFrom ,content,root_column_info_id as rootColumnInfoId , guid_read as guidRead ,cover_image_url as coverImageUrl from cms_article WHERE delete_flag=0 and is_audit=1 and id =  ");
        hql.append("'" + id + "'");
        return this.querySqlObjects(hql.toString());
    }

    public PageModel<Map<String, Object>> queryArticleListInWeek(Date date, PageBean pageBean) {
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuilder hql = new StringBuilder();
        hql.append(" SELECT id,DATE_FORMAT(update_date,'%Y-%m-%d') as updateDate ,title ,root_column_info_id as rootColumnInfoId FROM cms_article where delete_flag=0 and  is_audit =1 and update_date > :date  ORDER BY update_date desc");
        map.put("date", date);
        String baseSql = "SELECT count(*) FROM cms_article where delete_flag=0 and  is_audit =1 and  update_date > :date";
        return this.querySqlObjects(hql.toString(), baseSql, map, pageBean.getCurrentPage() + 1, pageBean.getPageSize());
    }

    public PageModel<Map<String, Object>> getArticleListByColumnInfoIdList(ArticleFontQueryDTO articleFontQueryDTO) {
        StringBuilder hql = new StringBuilder();
        hql.append(" SELECT id,DATE_FORMAT(update_date,'%Y-%m-%d') as updateDate,guid_read as guidRead ,adcode,activity_detail as activityDetail,organizers,DATE_FORMAT(holding_time,'%Y-%m-%d') as holdingTime,title ,content,cover_image_url as coverImageUrl, root_column_info_id as rootColumnInfoId  from cms_article WHERE delete_flag=0 and is_audit=1 ");
        List<String> columnInfoIdList = articleFontQueryDTO.getColumnInfoIdList();
        if (articleFontQueryDTO != null) {
            if (!CollectionUtils.isEmpty(columnInfoIdList)) {
                hql.append(" and column_info_id in (");
                for (int i = 0; i < columnInfoIdList.size(); i++) {
                    if (i == columnInfoIdList.size() - 1) {
                        hql.append(" '" + columnInfoIdList.get(i).trim().toString() + "'" + ")");
                    } else {
                        hql.append(" '" + columnInfoIdList.get(i).trim().toString() + "'" + ",");
                    }
                }
            }
            String columnInfoId = articleFontQueryDTO.getColumnInfoId();
            if (StringUtils.isNotBlank(columnInfoId)) {
                hql.append(" and column_info_id =" + "'" + columnInfoId + "'");
            }
            String adcode = articleFontQueryDTO.getAdcode();
            if (StringUtils.isNotBlank(adcode)) {
                hql.append(" and adcode =" + "'" + adcode + "'");
            }
            String id = articleFontQueryDTO.getId();
            if (StringUtils.isNotBlank(id)) {
                hql.append(" and id =" + "'" + id + "'");
            }

            String title = articleFontQueryDTO.getTitle();
            if (StringUtils.isNotBlank(title)) {
                hql.append(" and title =" + "'" + title + "'");
            }

            String parentColumnInfoId = articleFontQueryDTO.getParentColumnInfoId();
            if (StringUtils.isNotBlank(parentColumnInfoId)) {
                hql.append(" and root_column_info_id =" + "'" + parentColumnInfoId + "'");
            }
            String holdingTimeStart = articleFontQueryDTO.getHoldingTime();
            if (StringUtils.isNotBlank(holdingTimeStart)) {
                String holdingTimeEnd = DateUtils.geDateByDelayMonth(holdingTimeStart, 1);
                hql.append(" and holding_time > " + "'" + holdingTimeStart + "'");
                hql.append(" and holding_time < " + "'" + holdingTimeEnd + "'");
            }
        }

        hql.append(" ORDER BY update_date desc ");
        return this.queryForPageBySql(hql.toString(), articleFontQueryDTO.getCurrentPage() + 1, articleFontQueryDTO.getPageSize());
    }

    public int countByRootColumnInfoId(String rootColumnInfoId) {
        StringBuilder hql = new StringBuilder();
        Map<String, Object> map = new HashMap<String, Object>();

        hql.append(" SELECT count(*) from cms_article WHERE root_column_info_id=:rootColumnInfoId  ");
        if (StringUtils.isNotBlank(rootColumnInfoId)) {
            map.put("rootColumnInfoId", rootColumnInfoId);
        }
        return this.queryCountBySql(hql.toString(), map);
    }

    @Override
    public PageModel<Map<String, Object>> getByKewWord(SearchKewWordDto searchKewWordDto) {
        StringBuilder hql = new StringBuilder();
        String kewWord = searchKewWordDto.getKewWord();
        hql.append(" SELECT title,id ,cover_image_url as image,guid_read as guidRead, date_format( create_date, '%Y-%m-%d' ) AS createDate ");
        String id = searchKewWordDto.getId();
        if (!StringUtils.isEmpty(id)) {
            hql.append(",content ");
        }
        hql.append("from cms_article WHERE delete_flag=0 and is_audit=1");
        if (!StringUtils.isEmpty(kewWord)) {
            hql.append(" and  title like '%" + kewWord + "%'");
        }
        if (!StringUtils.isEmpty(id)) {
            hql.append(" and id = '" + id+"'");
        }
        return this.queryForPageBySql(hql.toString(), searchKewWordDto.getCurrentPage() + 1, searchKewWordDto.getPageSize());
    }


    public PageModel<Map<String, Object>> getArticleSimpleListByColumnInfoIdList(ArticleFontQueryDTO articleFontQueryDTO) {
        StringBuilder hql = new StringBuilder();
        hql.append(" SELECT id,DATE_FORMAT(update_date,'%Y-%m-%d') as updateDate,guid_read as guidRead ,adcode,organizers,DATE_FORMAT(holding_time,'%Y-%m-%d') as holdingTime,title ,cover_image_url as coverImageUrl, root_column_info_id as rootColumnInfoId  from cms_article WHERE delete_flag=0 and is_audit=1 ");
        List<String> columnInfoIdList = articleFontQueryDTO.getColumnInfoIdList();
        if (articleFontQueryDTO != null) {
            if (!CollectionUtils.isEmpty(columnInfoIdList)) {
                hql.append(" and column_info_id in (");
                for (int i = 0; i < columnInfoIdList.size(); i++) {
                    if (i == columnInfoIdList.size() - 1) {
                        hql.append(" '" + columnInfoIdList.get(i).trim().toString() + "'" + ")");
                    } else {
                        hql.append(" '" + columnInfoIdList.get(i).trim().toString() + "'" + ",");
                    }
                }
            }
            String columnInfoId = articleFontQueryDTO.getColumnInfoId();
            if (StringUtils.isNotBlank(columnInfoId)) {
                hql.append(" and column_info_id =" + "'" + columnInfoId + "'");
            }
            String adcode = articleFontQueryDTO.getAdcode();
            if (StringUtils.isNotBlank(adcode)) {
                hql.append(" and adcode =" + "'" + adcode + "'");
            }
            String id = articleFontQueryDTO.getId();
            if (StringUtils.isNotBlank(id)) {
                hql.append(" and id =" + "'" + id + "'");
            }

            String title = articleFontQueryDTO.getTitle();
            if (StringUtils.isNotBlank(title)) {
                hql.append(" and title =" + "'" + title + "'");
            }

            String parentColumnInfoId = articleFontQueryDTO.getParentColumnInfoId();
            if (StringUtils.isNotBlank(parentColumnInfoId)) {
                hql.append(" and root_column_info_id =" + "'" + parentColumnInfoId + "'");
            }
            String holdingTimeStart = articleFontQueryDTO.getHoldingTime();
            if (StringUtils.isNotBlank(holdingTimeStart)) {
                String holdingTimeEnd = DateUtils.geDateByDelayMonth(holdingTimeStart, 1);
                hql.append(" and holding_time > " + "'" + holdingTimeStart + "'");
                hql.append(" and holding_time < " + "'" + holdingTimeEnd + "'");
            }
        }

        hql.append(" ORDER BY update_date desc ");
        return this.queryForPageBySql(hql.toString(), articleFontQueryDTO.getCurrentPage() + 1, articleFontQueryDTO.getPageSize());
    }
}
